package com.gss.stockLedger.web;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import javax.mail.internet.MimeMessage;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.mail.javamail.MimeMessagePreparator;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.gss.stockLedger.dao.QueryStockDAO;
import com.gss.stockLedger.domain.StockTransactionDtls;
import com.gss.stockLedger.form.ReportGenerateForm;
import com.gss.stockLedger.form.StockTransactionForm;
import com.gss.stockLedger.form.UploadFileForm;
import com.gss.stockLedger.form.VendorWiseReportFilter;
import com.gss.stockLedger.service.FIFOReportGenerationService;
import com.gss.stockLedger.service.FifoReportGenerationThreadedService;
import com.gss.stockLedger.service.VendorWiseReportGenerationService;
import com.gss.stockLedger.service.WACHybridReportGenerationService;
import com.gss.stockLedger.service.WACReportGenerationService;
import com.gss.stockLedger.service.WACReportGenerationThreadedService;
import com.gss.stockLedger.service.WacHybridReportGenerationThreadedService;
import com.gss.stockLedger.util.ApplicationConstants;

@Controller
public class UploadDataController {
	
	Logger logger = Logger.getLogger(UploadDataController.class);
	
	@Autowired
	JavaMailSender mailSender;
	
	 public void setMailSender(JavaMailSender mailSender) {
		 this.mailSender = mailSender;
	 }
	
	@Autowired
	private QueryStockDAO queryStockDAO;
	
	@Autowired
	private FifoReportGenerationThreadedService fifoReportGenerationThreadedService;
	
	@Autowired
	private WACReportGenerationThreadedService wacReportGenerationThreadedService;
	
	@Autowired
	private WacHybridReportGenerationThreadedService wacHybridReportGenerationThreadedService;
	
	@Autowired
	private VendorWiseReportGenerationService vendorWiseReportGenerationService;
	
	@Autowired
	private WACHybridReportGenerationService wacHybridReportGenerationService;
	
	/*@Autowired
	private StockCalculationService calculationService;*/
	
	@Autowired
	private WACReportGenerationService wacReportGenerationService;
	
	@Autowired
	private FIFOReportGenerationService fifoReportGenerationService;

	@RequestMapping("/welcome.htm")
	public ModelAndView goToWelcomePage(){
		ModelAndView mav = new ModelAndView();
		mav.setViewName("welcome");
		return mav;
	}
	
	@RequestMapping("/getAddNewTransactionForm.htm")
	public ModelAndView addNewTransaction(){
		ModelAndView mav = new ModelAndView();
		StockTransactionForm stockTransactionForm = new StockTransactionForm();
		mav.addObject("stockTransactionForm", stockTransactionForm);
		mav.addObject("purchaseTypeMap",ApplicationConstants.PURCHASE_TYPE);
		mav.addObject("transactionTypeMap",ApplicationConstants.TRANSACTION_TYPE);
		mav.addObject("accountTypeMap",ApplicationConstants.ACCOUNT_TYPE);
		mav.setViewName("addNewTransaction");
		
		return mav;
	}
	
	@RequestMapping("/getReportPage.htm")
	public ModelAndView getReportPage()
	{
		ModelAndView modelAndView = new ModelAndView();
		modelAndView.setViewName("generateReport");
		return modelAndView;
		
	}
	
	@RequestMapping("/getStockLedgerReport")
	public ModelAndView getStockLedgerReport(@RequestParam("reportCategory") String reportCategory){
		logger.debug("reportCategory: " + reportCategory);
		ModelAndView modelAndView = new ModelAndView();
		ReportGenerateForm reportGenerateForm = new ReportGenerateForm();
		reportGenerateForm.setReportCategory(reportCategory);
		modelAndView.addObject("reportGenerateForm", reportGenerateForm);
		modelAndView.setViewName("storeLedgerReportFilters");
		return modelAndView;
	}
	
	@RequestMapping("/generateVendorWiseReportFilters.htm")
	public ModelAndView generateVendorWiseReportFilters(){
		ModelAndView modelAndView = new ModelAndView();
		
		VendorWiseReportFilter vendorWiseReportFilter = new VendorWiseReportFilter();
		modelAndView.addObject("vendorWiseReportFilter", vendorWiseReportFilter);
		modelAndView.setViewName("vendorWiseReportFilter");
		
		return modelAndView;
	}	
	
	@RequestMapping("/generateNegativePartReportFilters")
	public ModelAndView getNegativePartReportFilters(){
		
		ModelAndView modelAndView = new ModelAndView("negativePartReportFilters");
		ReportGenerateForm reportGenerateForm = new ReportGenerateForm();
		reportGenerateForm.setNegativePartVariable(true);
		reportGenerateForm.setValuationMethod("FIFO");
		reportGenerateForm.setReportCategory(ApplicationConstants.negativePartSheetName);
		
		modelAndView.addObject("reportGenerateForm", reportGenerateForm);
		return modelAndView;
	}
	
	public static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
	public static SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("ddMMyyyyHHmmss");
	
	@RequestMapping("/generateReport.htm")
	public void generateReport(@ModelAttribute ReportGenerateForm reportForm,@ModelAttribute VendorWiseReportFilter vendorWiseReportFilter,HttpServletResponse response) {
		Date date = new Date();
		boolean resultOfSucess = false;
		boolean isVendor = false;

		try {
			
			if(null != reportForm.getValuationMethod()){
				if(reportForm.getValuationMethod().equalsIgnoreCase("WAC")){
					resultOfSucess = wacReportGenerationThreadedService.generateWACReport(reportForm,date);
				}
				else if(reportForm.getValuationMethod().equalsIgnoreCase("FIFO")){
					resultOfSucess = fifoReportGenerationThreadedService.generateFIFOReport(reportForm,date);
				}
				else if(reportForm.getValuationMethod().equalsIgnoreCase("WACHybrid")){
					resultOfSucess = wacHybridReportGenerationThreadedService.generateWACReport(reportForm, date);
				}
			}
			else{
				isVendor = true;
				resultOfSucess = vendorWiseReportGenerationService.generateVendorWiseReport(vendorWiseReportFilter, date);
			}
			
			if(resultOfSucess){
				String pathName = null;
				String fileName = null;
				String webLinkPath = null;
				if(!isVendor && "StockLedger".equals(reportForm.getReportCategory())){
					
					pathName = ApplicationConstants.pathToExcel+ System.getProperty("file.separator") + reportForm.getValuationMethod() 
						+ System.getProperty("file.separator") + reportForm.getReportCategory() +  System.getProperty("file.separator") 
						+ reportForm.getReportType() + System.getProperty("file.separator") + simpleDateFormat.format(date) 
						+ System.getProperty("file.separator");
					
					fileName = reportForm.getValuationMethod() + simpleDateFormat.format(reportForm.getStartDate()) 
							+ simpleDateFormat.format(reportForm.getEndDate()) + simpleDateFormat2.format(date) +".xls";
					
					webLinkPath = System.getProperty("file.separator") + reportForm.getValuationMethod() 
							+ System.getProperty("file.separator") + reportForm.getReportCategory() +  System.getProperty("file.separator") 
							+ reportForm.getReportType() + System.getProperty("file.separator") + simpleDateFormat.format(date) 
							+ System.getProperty("file.separator") + reportForm.getValuationMethod() + simpleDateFormat.format(reportForm.getStartDate()) 
							+ simpleDateFormat.format(reportForm.getEndDate()) + simpleDateFormat2.format(date) +".xls";
				}
				else if(reportForm.isNegativePartVariable() 
						&& ApplicationConstants.negativePartSheetName.equals(reportForm.getReportCategory())){
	
					pathName = ApplicationConstants.pathToExcel + System.getProperty("file.separator")
							+ ApplicationConstants.negativePartSheetName + System.getProperty("file.separator")
							+ simpleDateFormat.format(date) + System.getProperty("file.separator");
					
					fileName = simpleDateFormat.format(reportForm.getStartDate()) 
							+ simpleDateFormat.format(reportForm.getEndDate()) + simpleDateFormat2.format(date) + ".xls";
					
					webLinkPath = System.getProperty("file.separator")
							+ ApplicationConstants.negativePartSheetName + System.getProperty("file.separator")
							+ simpleDateFormat.format(date) + System.getProperty("file.separator") + simpleDateFormat.format(reportForm.getStartDate()) 
							+ simpleDateFormat.format(reportForm.getEndDate()) + simpleDateFormat2.format(date) + ".xls";
				}
				else if(isVendor){
					pathName = ApplicationConstants.pathToExcel+ System.getProperty("file.separator") + ApplicationConstants.vendorWiseSheetName
							+ System.getProperty("file.separator") + simpleDateFormat.format(date) + System.getProperty("file.separator");
					
					fileName = simpleDateFormat.format(vendorWiseReportFilter.getStartDate()) 
							+ simpleDateFormat.format(vendorWiseReportFilter.getEndDate()) + simpleDateFormat2.format(date) + ".xls";
					
					webLinkPath = System.getProperty("file.separator") + ApplicationConstants.vendorWiseSheetName
							+ System.getProperty("file.separator") + simpleDateFormat.format(date) + System.getProperty("file.separator") + 
							simpleDateFormat.format(vendorWiseReportFilter.getStartDate()) 
							+ simpleDateFormat.format(vendorWiseReportFilter.getEndDate()) + simpleDateFormat2.format(date) + ".xls";
				}
				
//				if((null != reportForm.getEmail() && !"".equalsIgnoreCase(reportForm.getEmail())) 
//						|| (null != vendorWiseReportFilter.getEmail() && !"".equalsIgnoreCase(vendorWiseReportFilter.getEmail()))){
					
					Properties properties = new Properties();
					properties.load(UploadDataController.class.getResourceAsStream("/stockLedger.properties"));
					String ccEmail = properties.getProperty("defaultEmail");
					String preUrl = properties.getProperty("preUrl");
					String reportDownloadLink = preUrl + webLinkPath;
					String emailBody = "";
					String toEmailId = null;
					if(!isVendor){
						emailBody = mailMessageForStockLedger(reportForm,reportDownloadLink);
						toEmailId = reportForm.getEmail();
					}
					else {
						emailBody = mailMessageForVendorWise(reportDownloadLink,vendorWiseReportFilter);
						toEmailId = vendorWiseReportFilter.getEmail();
					}
					
					String emailSubject = "Regarding report generation.";
					
					MailMessagePreparator mailMessagePreparator = null;
					if(null != toEmailId){
						mailMessagePreparator = new MailMessagePreparator(toEmailId, ccEmail , emailBody,emailSubject);
					}else{
						mailMessagePreparator = new MailMessagePreparator(ccEmail, null , emailBody,emailSubject);
					}
					
					boolean resultOfMailSending = false; 
					try{
						this.mailSender.send(mailMessagePreparator);
						resultOfMailSending = true;
					}
					catch (Exception e) {
						logger.error(e.getMessage(),e);
						resultOfMailSending = false;
					}

					if(resultOfMailSending)
						logger.debug("Mail on "+ (null!=toEmailId ? toEmailId : ccEmail) + " had been sent.");
					else
						logger.debug("Mail on "+ (null!=toEmailId ? toEmailId : ccEmail) + " had not been sent.");
//				}
//				else{
//					logger.debug("Email id not provided. No email sent.");
//				}
				try{
					response.setContentType("application/x-excel");
					response.addHeader("Content-Disposition", "filename="+ fileName);
					
					System.out.println("WEBLINKPATH: " + webLinkPath);
					System.out.println("Searching file name: " + (pathName + fileName));
					
					FileInputStream fileInputStream = new FileInputStream(pathName + fileName);
					
					OutputStream outputStream = response.getOutputStream();
					byte[] byteArray = new byte[2048];
					int temp = fileInputStream.read(byteArray);
					while(temp > 0){
						outputStream.write(byteArray, 0, temp);
						temp = fileInputStream.read(byteArray);
					}
					outputStream.flush();
					outputStream.close();
				}
				catch(Exception e){
					logger.debug(e.getMessage(), e);
				}
				
				
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		logger.debug("resultOfSucess"+resultOfSucess);
	}
	
	private String mailMessageForStockLedger(ReportGenerateForm reportForm, String reportDownloadLink){
		StringBuilder mailMessage = new StringBuilder();
		reportForm.getStartDate().setTime(reportForm.getStartDate().getTime() + 100);
		mailMessage.append("The requested report with the below selected criteria had been generated. <br><br>");
		mailMessage.append("Report Criteria: <br><br>");
		mailMessage.append("<table border='1'>");
		if(null != reportForm){
			logger.debug("inside method(mailMessage): fetching details from reportForm");
			
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getInvType())){
				mailMessage.append("<tr> <td> Inventory Type: &nbsp; </td> <td>" + reportForm.getInvType() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getPurchaseType())){
				mailMessage.append("<tr> <td> Purchase Type: &nbsp; </td> <td>" + reportForm.getPurchaseType() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getTransactionType())){
				mailMessage.append("<tr> <td> Transaction Type: &nbsp; </td> <td>" + reportForm.getTransactionType() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getAccountType())){
				mailMessage.append("<tr> <td> Account Type: &nbsp; </td> <td>" + reportForm.getAccountType() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getCompanyName())){
				mailMessage.append("<tr> <td> Company Name: &nbsp; </td> <td>" + reportForm.getCompanyName() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getStartDate().toString())){
				mailMessage.append("<tr> <td> Start Date: &nbsp; </td> <td>" + simpleDateFormat.format(reportForm.getStartDate()) + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getEndDate().toString())){
				mailMessage.append("<tr> <td> End Date: &nbsp; </td> <td>" + simpleDateFormat.format(reportForm.getEndDate()) + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getLastPartId())){
				mailMessage.append("<tr> <td> Part Id: &nbsp; </td> <td>" + reportForm.getLastPartId() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getValuationMethod())){
				mailMessage.append("<tr> <td> Valuation Method: &nbsp; </td> <td>" + reportForm.getValuationMethod() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getReportType())){
				mailMessage.append("<tr> <td> Report Type: &nbsp; </td> <td>" + reportForm.getReportType() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(reportForm.getReportCategory())){
				mailMessage.append("<tr> <td> Report Category: &nbsp; </td> <td>" + reportForm.getReportCategory() + "</td></tr>");
			}
		}
		mailMessage.append("</table><br><br>");
		mailMessage.append("Report can be downloaded from the following link: " + reportDownloadLink);
		
		return mailMessage.toString();
	}
	
	private String mailMessageForVendorWise(String reportDownloadLink, VendorWiseReportFilter vendorWiseReportFilter){
		StringBuilder mailMessage = new StringBuilder();
		vendorWiseReportFilter.getStartDate().setTime(vendorWiseReportFilter.getStartDate().getTime() + 100);
		mailMessage.append("The requested report with the below selected criteria had been generated. <br><br>");
		mailMessage.append("Report Criteria: <br><br>");
		mailMessage.append("<table border='1'>");
		
		if(null != vendorWiseReportFilter){
			logger.debug("inside method(mailMessage): fetching details from vendorWiseReportForm");
			if(!ApplicationConstants.checkIfNullOrBlank(vendorWiseReportFilter.getCompanyName())){
				mailMessage.append("<tr> <td> Company Name: &nbsp; </td> <td>" + vendorWiseReportFilter.getCompanyName() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(vendorWiseReportFilter.getSpecVendorCode())){
				mailMessage.append("<tr> <td> Vendor Code: &nbsp; </td> <td>" + vendorWiseReportFilter.getSpecVendorCode() + "</td></tr>");
			}		
			if(!ApplicationConstants.checkIfNullOrBlank(vendorWiseReportFilter.getVendorCode())){
				mailMessage.append("<tr> <td> Vendor Code: &nbsp; </td> <td>" + vendorWiseReportFilter.getVendorCode() + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(vendorWiseReportFilter.getStartDate().toString())){
				mailMessage.append("<tr> <td> Start Date: &nbsp; </td> <td>" + simpleDateFormat.format(vendorWiseReportFilter.getStartDate()) + "</td></tr>");
			}
			if(!ApplicationConstants.checkIfNullOrBlank(vendorWiseReportFilter.getEndDate().toString())){
				mailMessage.append("<tr> <td> End Date: &nbsp; </td> <td>" + simpleDateFormat.format(vendorWiseReportFilter.getEndDate()) + "</td></tr>");
			}
		}
		mailMessage.append("</table><br><br>");
		mailMessage.append("Report can be downloaded from the following link: " + reportDownloadLink);
		
		return mailMessage.toString();
	}
	
	@RequestMapping("generateVendorWiseReport.htm")
	public void generateVendorWiseReport(@ModelAttribute VendorWiseReportFilter vendorWiseReportFilter,
			HttpServletResponse httpServletResponse) throws IOException{
		Date date = new Date();
		boolean resultOfSuccess = vendorWiseReportGenerationService.generateVendorWiseReport(vendorWiseReportFilter,date);
		
		if(resultOfSuccess){
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd-MMM-yyyy");
			SimpleDateFormat simpleDateFormat2 = new SimpleDateFormat("ddMMyyyyHHmmss");
		}
	}
	
	@RequestMapping("/getUploadFileForm.htm")
	public ModelAndView showUploadFileForm(){
		ModelAndView mav = new ModelAndView();
		mav.addObject("uploadFileForm", new UploadFileForm());
		mav.setViewName("uploadExcel");
		return mav;
	}
	
	@RequestMapping("/uploadInputDataExcel.htm")
	public ModelAndView uploadExcel(@ModelAttribute UploadFileForm uploadFileForm){
		ModelAndView mav = new ModelAndView();
		MultipartFile inputFile = uploadFileForm.getInputFile();
		System.out.println(inputFile.getContentType() + " --- " + inputFile.getOriginalFilename());
		//HSSFWorkbook inputExcel = null;
		XSSFWorkbook inputExcel = null;
		XSSFSheet transactionSheet = null;
		List<StockTransactionDtls> transactionList = new ArrayList<StockTransactionDtls>();
		long commitedCounter = 0;
		long rowCount = 0;
		StringBuffer responseBuffer = new StringBuffer();
		try {
			//inputExcel  = new HSSFWorkbook(inputFile.getInputStream());
			inputExcel  = new XSSFWorkbook(inputFile.getInputStream());
			if(null != uploadFileForm.getWorkbookName() && !"".equals(uploadFileForm.getWorkbookName())){
				transactionSheet = inputExcel.getSheet(uploadFileForm.getWorkbookName());
				System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@ loading data from " + uploadFileForm.getWorkbookName());
			}
			else {
				transactionSheet = inputExcel.getSheetAt(0);
			}
			logger.debug("PRINT: ");
			XSSFRow headerRow =  transactionSheet.getRow(0);
			Iterator<Cell> iterator = headerRow.cellIterator();
			int columnCount = 0;
			while(iterator.hasNext()){
				System.out.print(" , " + iterator.next().getStringCellValue());
				columnCount++;
			}
			System.out.println(columnCount);
			
			Iterator<Row> rowIterator = transactionSheet.rowIterator();
			// skip header row
			rowIterator.next();
			XSSFRow transactionRow = null;
			while(rowIterator.hasNext()){
				transactionRow = (XSSFRow) rowIterator.next();
				transactionList.add(printRowContent(transactionRow, columnCount));
				rowCount++;
			}
			responseBuffer.append("Number of rows read from excel: " +rowCount + "\n");
			System.out.println("number of rows read: " + rowCount);
			
			try {
				commitedCounter = queryStockDAO.uploadStock(transactionList,uploadFileForm.getCompanyName());
			} catch (Throwable e) {
				e.printStackTrace();
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		inputExcel = null;
		responseBuffer.append("Number of rows added to database: " + commitedCounter + "\n");
		if(commitedCounter == rowCount){
			responseBuffer.append("Upload Successful.");
		}else{
			responseBuffer.append("Upload Unsuccessful.");
		}
		mav.addObject("responseMessage",responseBuffer.toString());
		mav.setViewName("fileUploadResult");
		return mav;
	}
	
	@InitBinder
	public void binder(WebDataBinder binder)
	{
		SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
		dateFormat.setLenient(false);
		binder.registerCustomEditor(Date.class, new CustomDateEditor(dateFormat, false));
	}
	
	private StockTransactionDtls printRowContent(XSSFRow row,int columnCount){
		StockTransactionDtls transactionDTO = new StockTransactionDtls();
		XSSFCell currentCell = null;
		transactionDTO.setCreatedDate(new Date());
		transactionDTO.setVersion(1);
		transactionDTO.setStrCreatedAuthor("backend");
		
		for(int i =0; i < columnCount;i++){
			currentCell = row.getCell(i);
			try{
				if(null != currentCell && null != currentCell.getRawValue()) {
			switch (i) {
			case 0:
				transactionDTO.setPartId(currentCell.toString());
				break;
			case 1:
				transactionDTO.setPartDesc(currentCell.toString());
				break;
			case 2:
				transactionDTO.setSapDocNo(currentCell.toString());
				break;
			case 3:
				transactionDTO.setSapEntryDate(currentCell.getStringCellValue());
				break;
			case 4:
				transactionDTO.setAssignmentId(currentCell.getStringCellValue());
				break;
			case 5:
				transactionDTO.setStrTransactionDate(currentCell.getStringCellValue());
				break;
			case 6:
				if(currentCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
					transactionDTO.setInvoiceNo(""+ currentCell.getNumericCellValue());
				else
					transactionDTO.setInvoiceNo(currentCell.getStringCellValue());
				break;
			case 7:
				transactionDTO.setStrInvoiceDate(currentCell.getStringCellValue());
				break;
			case 8:
				transactionDTO.setVendorCode(currentCell.getStringCellValue());
				break;
			case 9:
				transactionDTO.setVendorName(currentCell.getStringCellValue());
				break;
			case 10:
				transactionDTO.setInventoryType(currentCell.getStringCellValue());
				break;
			case 11:
				transactionDTO.setTransactionType(currentCell.getStringCellValue());
				break;
			case 12:
				transactionDTO.setAcctType(currentCell.getStringCellValue());
				break;
			case 13:
				if(currentCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
					transactionDTO.setQuantity((long)currentCell.getNumericCellValue());
				else
					transactionDTO.setQuantity(Long.getLong(currentCell.getStringCellValue()));
				break;
//			case 14:
//				transactionDTO.setUom(currentCell.getStringCellValue());
//				break;
			case 15:
				if(null != currentCell && null != currentCell.getRawValue()){
					if(currentCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
						transactionDTO.setRate((float)currentCell.getNumericCellValue());
					else
						transactionDTO.setRate(Float.parseFloat(currentCell.getStringCellValue()));
					}
				break;
			case 16:
				if(null != currentCell && null != currentCell.getRawValue()){
				if(currentCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
					transactionDTO.setValue(currentCell.getNumericCellValue());
				else
					transactionDTO.setValue(Double.parseDouble(currentCell.getStringCellValue()));
				}
				break;
			case 17:
				if(null != currentCell && null != currentCell.getRawValue()){
					transactionDTO.setPurchaseType(currentCell.getStringCellValue());
				}
				break;
			case 19:
				if(null != currentCell && null != currentCell.getRawValue()){
					transactionDTO.setUnitFCY(currentCell.getStringCellValue());
				}				
				break;
			case 18:
				if(null != currentCell && null != currentCell.getRawValue()){
					if(currentCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
						transactionDTO.setValueFCY((double)currentCell.getNumericCellValue());
					else
						transactionDTO.setValueFCY(Double.parseDouble(currentCell.getStringCellValue()));
					}
				break;
			default:
				System.out.print("value at column index " + i + " ignored , ");
				break;
			}
				}
			}
			catch (Exception e) {
				e.printStackTrace();
				logger.error("ERROR IN UPLOADING EXCEL : " , e );
				System.out.println(i + " -- " + e.getMessage() + " -- " + currentCell.getCellType());
			}
		}
		
		return transactionDTO;
		//System.out.println();
	}
	
	public class MailMessagePreparator implements MimeMessagePreparator {

		private final String toEmail;
		private final String ccEmail;
		private final String emailBody;
		private final String emailSubject;
		
		public MailMessagePreparator(String toEmail,String toCC,String emailBody, String emailSubject){
			logger.debug("inside constructor(MailMessagePreparator)");
			this.toEmail = toEmail;
			this.ccEmail = toCC;
			this.emailBody = emailBody;
			this.emailSubject = emailSubject;
		}
		
		public void prepare(MimeMessage mimeMessage) throws Exception {
			logger.debug("inside class MailMessagePreparator: method(prepare)");
			MimeMessageHelper messageHelper = new MimeMessageHelper(mimeMessage);
			
			if(null != toEmail && toEmail.length() > 0)
				messageHelper.setTo(toEmail);
    		if(null != ccEmail && ccEmail.length() > 0)
    			messageHelper.setCc(ccEmail);
    		
    		messageHelper.setFrom("STOCKAdministrator");
    		messageHelper.setText(emailBody,true);
    		messageHelper.setSubject(emailSubject);
			
		}

		public String getToEmail() {
			return toEmail;
		}

		public String getCcEmail() {
			return ccEmail;
		}

		public String getEmailBody() {
			return emailBody;
		}

		public String getEmailSubject() {
			return emailSubject;
		}


		
	}
}
